<?php
/**
 * This file contains the router for the project.
 * This is all "business" logic
 */

class App {	
	//public static $databases = array();
	//public static $currentDB;
	public static $mysql;
	
	/**
	 * Make sure we have a database connection
	 * 
	 * @return Boolean True if a connection exists, false if it doesn't
	 */
	function login(){
		if ( isset($_POST['dbhost']) ){
			$_SESSION['dbhost'] = $_POST['dbhost'];
			$_SESSION['dbuser'] = $_POST['dbuser'];
			$_SESSION['dbpass'] = $_POST['dbpass'];	
		}
		
		if ( ! isset($_SESSION['dbhost']) || ! isset($_SESSION['dbuser']) ){
			HTTP::redirect('auth');
			exit;
		}
		
		$dbhost	= $_SESSION['dbhost'];
		$dbuser	= $_SESSION['dbuser'];
		$dbpass	= $_SESSION['dbpass'];
		
		// Ensure we have a database connection
		if (! MySQL::connect($dbhost, $dbuser, $dbpass) ) return false;
		
		return true;
	}
	
	function route($resource, $action, $id){
		session_start();
		
		// If we're not on the login page, try to log us in
		if ( $resource != 'auth' ) App::login() or HTTP::error("Couldn't connect to the database");

		switch ( $resource ){
			
			case 'test':
				Config::$debug = true;
				App::$mysql->createDatabase('Demo3')->createTable('Test');
				HTML::dump(App::$mysql->database('Demo3')->table('Test')->fields());
				//$t = Table::retrieve("Demo.Artists");
				//HTML::dump($t->parentOptions());
				//$t->ancestors();
				//$t->descendants();
				//HTML::dump($t->joinClause());
				break;
			/**
			 * LOGIN
			 */
			case 'auth':
				
				switch ( $action ){
					case 'login':
						if ($_POST){
							// Validation
							if ( $_POST['dbhost'] == '' ){
								Utility::setFlash("You didn't enter a server dummy.");
								HTTP::redirect('auth');
							}
							if ( App::login() ){
								HTTP::redirect('db');
							} else {
								Utility::setFlash(App::mysqlCuteError());
								HTTP::redirect('auth');
							}
						}
						HTTP::redirect('auth');
						break;
					case 'logout':
						session_destroy();
						HTTP::redirect('auth');
						break;
					default:
						$dbhost = isset($_SESSION['dbhost']) ? $_SESSION['dbhost'] : '';
						$dbuser = isset($_SESSION['dbuser']) ? $_SESSION['dbuser'] : '';
						$params = compact('dbhost', 'dbuser');
						Controller::loadView('connect', 'Zanzo!', $params);
				}
				break;
			
			/**
			 * DATABASE
			 */
			case 'db':
				if ( $id != '' ){
					$db = Database::retrieve($id);
					if ( ! $db ) HTTP::notFound();
				}
				
				switch ( $action ){
					case 'list':
						Controller::loadView('database_list', MySQL::$host);
						break;
						
					case 'show':
						Controller::loadView('database_show', $db->name, array('db' => $db));
						break;
					
					case 'create':
						if ($_POST){
							Database::create($_POST['dbName']);
						}
						HTTP::redirect('db', 'list');
						break;
						
					case 'delete':
						if ($_POST){
							$db->delete();
						}
						HTTP::redirect('db', 'list');
						break;
						
					case 'create_table':
						$redir = $db->id;
						if ($_POST){
							$table = Table::create($db, $_POST['tableName']);
							$redir .= "#tbl_".$table->id;
						}
						HTTP::redirect('db', 'show', $redir);
				}
				break;
				
			/**
			 * TABLE
			 */
			case 'table':
				if ( $id != '' ){
					$tableID = $id;
					$table	= Table::retrieve($id);
					$db		= $table->db;	
				}
				
				switch ( $action ){						
					case 'show':
						Controller::loadView('table_show', $table->db->name." » ".$table->name, array('table' => $table));
						break;
						
					case 'update':
						$redir = $db->id;
						if ($_POST){
							MySQL::selectDatabase($db->name);
							// Update any existing fields
							$fieldOrder = array();
							if (isset($_POST["fields"]) ){
								foreach ( $_POST["fields"] as $id => $postField ){
									$field = $table->findField($id);
									if ( isset($postField['delete']) ){
										$field->delete();
										continue;
									}
									$field->update($postField['name'], $postField['type']);
									if ( $postField['after'] ) $fieldOrder[$id] = $postField['after'];
								}
							}
							// Add our new field
							if ( $_POST['newFieldType'] != '' ){
								$fieldType	= $_POST['newFieldType'];
								$fieldName	= $_POST['newFieldName'];
								
								$newField = Field::create($table, $fieldName, $fieldType);
								
								if ( $_POST['newFieldAfter'] ) $fieldOrder[$newField->id] = $_POST['newFieldAfter'];
							}
							$table->reorderFields($fieldOrder);
							$redir .= "#tbl_".$table->id;
						}
						HTTP::redirect('db', 'show', $redir);
						break;
						
					case 'delete':
						if ($_POST){
							$table->delete();
						}
						HTTP::redirect('db', 'show', $table->db->id);
						break;
						
					case 'rename':
						if ($_POST){
							$table->update($_POST['rename']);
						}
						HTTP::redirect('db', 'show', $table->db->id);
						break;
						
					case 'update_records':
						// TODO: pass this through model???
						if ($_POST){
							MySQL::selectDatabase($table->db->name);
							
							foreach ( $_POST['new'] as $field => $value){
								if ( $value == '' ) continue;
								// TODO: validate
								MySQL::tableCreateRecord($table->name, $_POST['new']) or HTTP::mySQLError();
								break;
							}
							
							if (isset($_POST['update'])){
								foreach ( $_POST['update'] as $id ){
									// Validate
									MySQL::tableUpdateRecord($table->name, $id, $_POST['record'][$id]) or HTTP::mySQLError();
								}
							}
							
							if (isset($_POST['delete'])){
								foreach ( $_POST['delete'] as $id ){
									print $no;
									// Validate
									MySQL::tableDeleteRecord($table->name, $id) or HTTP::mySQLError();
								}
							}
						}
						HTTP::redirect('table', 'show', $table->id);
						
						break;
					
					case 'new_vfield':
						Controller::loadView('vfield_new', 'New Virtual Field', array('table' => $table));
						break;
						
					case 'sql_vfield':
						Controller::loadView('vfield_new', 'New Virtual Field', array('table' => $table, 'vFieldType' => 'sql'));
						break;
						
					case 'summary_vfield':
						Controller::loadView('vfield_new', 'New Virtual Field', array('table' => $table, 'vFieldType' => 'summary'));
						break;
						
					case 'parent_vfield':
						Controller::loadView('vfield_new', 'New Virtual Field', array('table' => $table, 'vFieldType' => 'parent'));
						break;
						
					case 'create_vfield':
						if ($_POST){
							$name = $_POST['name'];
							switch ( $_POST['vFieldType'] ){
								case 'sql':
									$sql = $_POST['sql'];
									break;
								
								case 'summary':
									$fields = explode('.', $_POST['summaryField']);
									$summaryTable = join('.', array_splice($fields, 0, count($fields)-1));
									$summaryField = $fields[count($fields)-1];
									$summaryType = Types::$summaryTypes[$_POST['summaryType']];
									$sql = sprintf("%s(`%s`.`%s`)", $summaryType, $summaryTable, $summaryField);
									break;
								
								case 'parent':
									$fields = explode('.', $_POST['parentField']);
									$summaryTable = join('.', array_splice($fields, 0, count($fields)-1));
									$summaryField = $fields[count($fields)-1];
									$sql = sprintf("`%s`.`%s`", $summaryTable, $summaryField);
							}
							
							VirtualField::create($table, $name, $sql);
						}
						HTTP::redirect('db', 'show', $table->db->id."#tbl_".$table->id);
				}
				break;
			
			default:
				HTTP::notFound();
		}
	}
	
	static function mysqlCuteError(){
		$host = MySQL::$host;
		switch ( mysql_errno() ){
			case 2003:
				return "Couldn't connect to MySQL on '$host'. Maybe it's off for lunch?";
			case 2005:
				return "'$host' doesn't seem to exist.";
			case 1045:
				return "Hard luck pal. Access denied.";
			default:
				return mysql_errno().' - '.mysql_error();
		}
	}
}

/**
 * Table class:
 */
/*
class Table extends Controller {

	
	function update(){
		if ($_POST){
			MySQL::selectDatabase($this->db->name) or HTTP::mySQLError();
			
			// Update any existing fields
			if (isset($_POST["fieldType"])){
				foreach ($_POST["fieldType"] as $id => $type){					
					if ( $type == 'Unknown' || $type == 'Virtual' || $_POST['afterField'][$id] != '') continue;
					
					$colname = $_POST["fieldName"][$id];
					$oldcolname = $_POST["fieldOldName"][$id];
					$coltype = Field::$sqlTypes[$type];

					$oldcolname = $type == 'Reference' ? "${oldcolname}_id" : $oldcolname;
					$colname = $type == 'Reference' ? "${colname}_id" : $colname;
				
					if (isset($_POST["delField"][$id])){
						MySQL::tableDeleteColumn($this->name, $colname) or HTTP::mySQLError();
					} else {
						MySQL::tableAlterColumn($this->name, $oldcolname, $colname, $coltype) or HTTP::mySQLError();
					}
				}
				
				// Now do it again but only for fields where
				// the order's changed. This ensures we get the right order
				foreach ( $_POST['afterField'] as $id => $afterField ){
					if ( $type == 'Unknown' || $_POST['afterField'][$id] == '') continue;
					
					$colname = $_POST["fieldName"][$id];
					$oldcolname = $_POST["fieldOldName"][$id];
					$coltype = Field::$sqlTypes[$type];

					$oldcolname = $type == 'Reference' ? "${oldcolname}_id" : $oldcolname;
					$colname = $type == 'Reference' ? "${colname}_id" : $colname;
				
					if (isset($_POST["delField"][$id])){
						MySQL::tableDeleteColumn($this->name, $colname) or HTTP::mySQLError();
					} else {
						MySQL::tableAlterColumn($this->name, $oldcolname, $colname, $coltype, $afterField) or HTTP::mySQLError();
					}
				}
				
				// Finally, insert out new field
				if ( $_POST['newFieldType'] != '' ){
					// TODO: validate
					$colname = $_POST['newFieldName'];				
					$coltype = Field::$sqlTypes[$_POST['newFieldType']];
					$after = $_POST['newFieldAfter'];
					
					$colname = $_POST['newFieldType'] == 'Reference' ? "${colname}_id" : $colname;
					
					MySQL::tableAddColumn($this->name, $colname, $coltype, $after) or HTTP::mySQLError();
				}
			}
		}		
		HTTP::redirect('db', 'show', $this->db->id."#tbl_".$this->name);
	}
	
	function getRecords(){
		MySQL::selectDatabase($this->db->name) or HTTP::mySQLError();
		$rows = MySQL::tableRecords($this->name, $this->getVirtualFields());
		if ( $rows === false ) HTTP::mySQLError();
		return $rows;
	}
	
	function getRefOptions($ref){
		MySQL::selectDatabase($this->db->name);
		$rows		= MySQL::tableRecords($ref) or HTTP::mySQLError();
		$options = array();
		foreach ( $rows as $row ){
			$options[$row['id']] = $row[1];
		}
		return $options;
		
	}
	
	function update_rows(){
		if ($_POST){
			MySQL::selectDatabase($this->db->name);
			
			foreach ( $_POST['new'] as $field => $value){
				if ( $value == '' ) continue;
				// TODO: validate
				MySQL::tableCreateRecord($this->name, $_POST['new']) or HTTP::mySQLError();
				break;
			}
			
			if (isset($_POST['update'])){
				foreach ( $_POST['update'] as $id ){
					// Validate
					MySQL::tableUpdateRecord($this->name, $id, $_POST['row'][$id]) or HTTP::mySQLError();
				}
			}
			
			if (isset($_POST['delete'])){
				foreach ( $_POST['delete'] as $id ){
					print $no;
					// Validate
					MySQL::tableDeleteRecord($this->name, $id) or HTTP::mySQLError();
				}
			}
		}
		HTTP::redirect('table', 'show', $this->id);
	}
	
	function sql_field(){
		$this->loadView('vcol_new', 'New Virtual Field', array('vFieldType'=>'sql'));
	}
	
	function new_vcol(){
		$this->loadView('vcol_new', 'New Virtual Field');
	}
	
	function create_vfield(){
		
	}
	
	function descendant_fields($incSiblings=false){
		$childFields = array();
		foreach ( $this->fields as $field ){
			if ( $field['type'] == 'Reference' ){
				$childTable = $this->db->getTable($field['reference']);
				$childFields[$childTable->name] = $childTable->descendant_fields(true);
				//$childFields = $childTable->child_fields();
			} else {
				if ( $incSiblings ) $childFields[$field['name']] = $field;
			}
		}
		return $childFields;
	}
	
	function getAfterFieldOpts(){
		$opts = array();
		$opts['[First]'] = '[First]';
		foreach ( $this->fields as $field ){
			// Are we on the last field?
			if ( $field == $this->fields[count($this->fields)-1] ) $name = '[Last]';
			else $name = $field['disp_name'];
			$opts[$field['name']] = $name;
		}
		return $opts;
	}
}
*/
/**
 * Attribute class: TODO
 */
/*
class Field {
	public static $sqlTypes = array(
		'Short String'	=> 'varchar(30)',
		'Long String'	=> 'varchar(100)',
		'Number'		=> 'int(10)',
		'Decimal'		=> 'decimal(10,4)',
		'Boolean'		=> "enum('N','Y')",
		'Date/Time'		=> "datetime",
		'Reference'		=> "int(11)",
	);
	
	static function zType($sqlType){
		$zTypes = array_flip(Field::$sqlTypes);
		$zType = isset($zTypes[$sqlType]) ? $zTypes[$sqlType]: 'Unknown';
		return $zType;
	}
	
	
	static function isField($field){
		return isset($field['type']) && isset($field['table']);
	}
}
*/
/**
 * Virtual Field class
 */
/*
class VirtualField extends Field {
	public static $combinationFunctions = array(
		'Average'	=> 'AVG',
		'Count'		=> 'COUNT',
		'Max'		=> 'MAX',
		'Min'		=> 'MIN',
		'Sum'		=> 'SUM'
	);
	
}*/
/**
 * Report class
 */
/*
class Report {
	
	function safeCreate($db, $name){
		if ( ! $db->hasVirtual ){
			// Check if we have permissions?
			MySQL::tableCreate('zReports') or HTTP::mySQLError();;
		}
		$reports = MySQL::tableRecordsWhere('zReports', "`name` = '$name'");
		if ( ! $reports ){
			return MySQL::tableCreateRecord('zReports', array('name'=>$name));
		}
		return $reports[0]['id'];
	}
	
	function getReport($name){
		
	}
}
*/